package com.example.computer_exam_training_sys.mapper;

import com.example.computer_exam_training_sys.entity.UserStudyStatistics;
import com.example.computer_exam_training_sys.entity.UserStudyStatisticsWithAnalysis;
import org.apache.ibatis.annotations.*;

import java.time.LocalDate;
import java.util.List;

@Mapper
public interface UserStudyStatisticsMapper {
    
    @Results(id = "statisticsMap", value = {
        @Result(property = "statisticsId", column = "stat_id"),
        @Result(property = "userId", column = "user_id"),
        @Result(property = "statisticsDate", column = "stat_date"),
        @Result(property = "totalStudyTime", column = "total_study_time"),
        @Result(property = "questionCount", column = "question_count"),
        @Result(property = "correctCount", column = "correct_count"),
        @Result(property = "practiceCount", column = "practice_count"),
        @Result(property = "examCount", column = "exam_count"),
        @Result(property = "createTime", column = "create_time")
    })
    @Select("SELECT * FROM user_study_statistics WHERE user_id = #{userId} AND stat_date = #{date}")
    UserStudyStatistics findByUserIdAndDate(@Param("userId") Long userId, @Param("date") LocalDate date);

    @ResultMap("statisticsMap")
    @Select("SELECT * FROM user_study_statistics WHERE user_id = #{userId} AND stat_date BETWEEN #{startDate} AND #{endDate}")
    List<UserStudyStatistics> findByUserIdAndDateRange(
            @Param("userId") Long userId, 
            @Param("startDate") LocalDate startDate, 
            @Param("endDate") LocalDate endDate);

    @Insert("INSERT INTO user_study_statistics (user_id, stat_date, total_study_time, question_count, " +
            "correct_count, practice_count, exam_count, create_time) " +
            "VALUES (#{userId}, #{statisticsDate}, #{totalStudyTime}, #{questionCount}, " +
            "#{correctCount}, #{practiceCount}, #{examCount}, NOW())")
    @Options(useGeneratedKeys = true, keyProperty = "statisticsId")
    int insert(UserStudyStatistics statistics);

    @Update("UPDATE user_study_statistics SET total_study_time = #{totalStudyTime}, " +
            "question_count = #{questionCount}, correct_count = #{correctCount}, " +
            "practice_count = #{practiceCount}, exam_count = #{examCount} " +
            "WHERE stat_id = #{statisticsId}")
    int update(UserStudyStatistics statistics);

    @Select("SELECT SUM(total_study_time) FROM user_study_statistics WHERE user_id = #{userId}")
    Integer getTotalStudyTime(Long userId);

    @Select("SELECT SUM(question_count) FROM user_study_statistics WHERE user_id = #{userId}")
    Integer getTotalQuestionCount(Long userId);

    @Select("SELECT SUM(correct_count) FROM user_study_statistics WHERE user_id = #{userId}")
    Integer getTotalCorrectCount(Long userId);

    @Results(id = "statisticsWithAnalysisMap", value = {
        @Result(property = "statisticsId", column = "stat_id"),
        @Result(property = "userId", column = "user_id"),
        @Result(property = "statisticsDate", column = "stat_date"),
        @Result(property = "totalStudyTime", column = "total_study_time"),
        @Result(property = "questionCount", column = "question_count"),
        @Result(property = "correctCount", column = "correct_count"),
        @Result(property = "practiceCount", column = "practice_count"),
        @Result(property = "examCount", column = "exam_count"),
        @Result(property = "createTime", column = "create_time"),
        @Result(property = "accuracyRate", column = "accuracy_rate"),
        @Result(property = "weakPoints", column = "weak_points"),
        @Result(property = "studyTrend", column = "study_trend")
    })
    @Select("SELECT s.*, a.accuracy_rate, a.weak_points, a.study_trend " +
            "FROM user_study_statistics s " +
            "LEFT JOIN user_analysis a ON s.user_id = a.user_id AND s.stat_date = a.practice_range_start " +
            "WHERE s.user_id = #{userId} AND s.stat_date = #{date}")
    UserStudyStatisticsWithAnalysis findByUserIdAndDateWithAnalysis(
            @Param("userId") Long userId, @Param("date") LocalDate date);

    @ResultMap("statisticsWithAnalysisMap")
    @Select("SELECT s.*, a.accuracy_rate, a.weak_points, a.study_trend " +
            "FROM user_study_statistics s " +
            "LEFT JOIN user_analysis a ON s.user_id = a.user_id AND s.stat_date BETWEEN a.practice_range_start AND a.practice_range_end " +
            "WHERE s.user_id = #{userId} AND s.stat_date BETWEEN #{startDate} AND #{endDate}")
    List<UserStudyStatisticsWithAnalysis> findByUserIdAndDateRangeWithAnalysis(
            @Param("userId") Long userId, 
            @Param("startDate") LocalDate startDate, 
            @Param("endDate") LocalDate endDate);
            
    // 从user_practices表获取练习次数
    @Select("SELECT COUNT(*) FROM user_practices WHERE user_id = #{userId} AND DATE(start_time) = #{date}")
    Integer getPracticeCountByUserAndDate(@Param("userId") Long userId, @Param("date") LocalDate date);
    
    // 从user_practices表获取练习题目数量
    @Select("SELECT SUM(question_count) FROM user_practices WHERE user_id = #{userId} AND DATE(start_time) = #{date}")
    Integer getQuestionCountFromPracticeByUserAndDate(@Param("userId") Long userId, @Param("date") LocalDate date);
    
    // 从user_practices表获取正确题目数量
    @Select("SELECT SUM(correct_count) FROM user_practices WHERE user_id = #{userId} AND DATE(start_time) = #{date}")
    Integer getCorrectCountFromPracticeByUserAndDate(@Param("userId") Long userId, @Param("date") LocalDate date);
    
    // 从user_exams表获取考试次数
    @Select("SELECT COUNT(*) FROM user_exams WHERE user_id = #{userId} AND DATE(create_time) = #{date}")
    Integer getExamCountByUserAndDate(@Param("userId") Long userId, @Param("date") LocalDate date);
    
    // 从user_exams和user_exam_details表获取考试题目数量
    @Select("SELECT COUNT(*) FROM user_exam_details d " +
            "INNER JOIN user_exams e ON d.exam_id = e.exam_id " +
            "WHERE e.user_id = #{userId} AND DATE(e.create_time) = #{date}")
    Integer getQuestionCountFromExamByUserAndDate(@Param("userId") Long userId, @Param("date") LocalDate date);
    
    // 从user_exams和user_exam_details表获取正确题目数量
    @Select("SELECT COUNT(*) FROM user_exam_details d " +
            "INNER JOIN user_exams e ON d.exam_id = e.exam_id " +
            "WHERE e.user_id = #{userId} AND DATE(e.create_time) = #{date} AND d.is_correct = 1")
    Integer getCorrectCountFromExamByUserAndDate(@Param("userId") Long userId, @Param("date") LocalDate date);
    
    // 从user_learning_progress表获取学习时间
    @Select("SELECT SUM(total_study_time) FROM user_learning_progress " +
            "WHERE user_id = #{userId} AND DATE(last_study_time) = #{date}")
    Integer getStudyTimeByUserAndDate(@Param("userId") Long userId, @Param("date") LocalDate date);
    
    // 获取用户所有的课程ID
    @Select("SELECT DISTINCT course_id FROM user_learning_progress WHERE user_id = #{userId}")
    List<Long> getCourseIdsByUserId(@Param("userId") Long userId);
} 